Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Parallelization

If the Oracle Parallel Query option is used and the statement being executed is parallelizable, the following steps take place:

1.  The query coordinator determines which operations can be performed in parallel.
2.  The query coordinator determines how many query servers to enlist.
3.  The query coordinator enlists query servers that perform the query.
4.  The query coordinator reassembles the resulting data and passes it back to the user.

The degree of parallelism is determined using the following order of precedence:

1.  Query hints. User-defined hints included in the SQL statement have the highest precedence.
2.  Table definition. The default degree of parallelism defined for the table is second in the order of precedence.
3.  Initialization parameters. Finally, the Oracle initialization parameters are used to determine parallelism.

The processes that execute the query are taken from the set of query servers available in the query server pool. This number is specified by the Oracle initialization parameter PARALLEL_MAX_SERVERS.

Fetch Rows To Be Returned

If the statement is a query, the final step in the processing of the SQL statement involves fetching the returned data in a loop until all the requested data has been returned to the user process.

Review of SQL Statement Processing

By understanding the process that takes place when an SQL statement is executed, you can see the value in avoiding some of these steps.

Taking advantage of SQL statements that have already been parsed is one way to limit the amount of overhead associated with the processing of the statement.

Summary

Badly formed SQL statements can nullify any optimizations you have made to the client or the server. If the SQL statements require unnecessary table scans or unnecessary processing, an optimized server can be brought to a standstill. To have the most-optimal system possible, each component—the client, the SQL statements, the network, and the server—should be tuned to take maximum advantage of available resources. The best way to improve performance is to reduce unnecessary processing and I/O.

You can do this by designing your SQL statements to take advantage of such features as the shared pool (where the library cache contains the shared SQL area). By designing your application so that it uses the same SQL statements to access the same data, you stand a good chance of your statements being in the library cache.

If you have long-running queries that perform tables scans, take advantage of the Parallel Query option. By using this feature, you can cut down on the time it takes to perform the query.

The use of other Oracle features such as stored procedures and packages can also maximize the effectiveness of your SQL statement processing and provide for more efficient coding. All these things can help improve the overall performance of your system.

The remaining chapters in Part IV of this book look into ways you can make your SQL statements more efficient—and how you can determine the efficiency of your SQL statements.

Of all the areas of the system that can be tuned, SQL statements offer the most potential. This is true not because SQL statement can be more effectively optimized, but because a badly tuned SQL statement can degrade performance drastically. As you will see, it is very important to have well-tuned SQL statements at the heart of your application.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.